/*
	exec sp_shipment_report_by_lot_number '0','0','0','0',1,'0','0','XLREPORT','0'
	
*/
alter PROC sp_shipment_report_by_lot_number

@customerIDs	varchar(200),
@productIDS	varchar(200),
@sonumber	varchar(50),
@lotNumber	varchar(50),
@orderType	int,
@fromdate	varchar(50),
@todate		varchar(50),
@REPORT_TYPE	VARCHAR(20),
@defaultPrompt	varchar(50)

as
BEGIN
	create table  #SHIPMENT_TABLE_BY_LOT_NUMBER
			([LINE ITEM NUMBER] INT,[COOL QTY] INT,[UNIT COST] FLOAT,
			 [SALEORDER NO] VARCHAR(50), [CUSTOMER PO NO] VARCHAR(50),
			 [END CUSTOMER] VARCHAR(65),CUSTOMER VARCHAR(65),[END USER] VARCHAR(65),
			 [QTY SHIPPED] INT,[PART NUMBER] VARCHAR(50),[LOT QTY PULLED] INT,
			 [PACKING SLIP NO] VARCHAR(50),[SHIP DATE] DATETIME, 
			 [INVOICE NUMBER] VARCHAR(50),[LOT NUMBER] VARCHAR(50),[DATE CODE] VARCHAR(50) 
			)

	DECLARE @SQLSTRING NVARCHAR(4000)
	
	--SELECT * FROM #ADDRESS_TABLE
	SET @SQLSTRING = N'INSERT INTO #SHIPMENT_TABLE_BY_LOT_NUMBER
	SELECT
   		COOL_LINE_ITEM_NO,COOL_QTY,COOL_UNIT_COST,
		SALESORDER_NO,SO_CUSTOMER_PO_NUMBER,
				(CASE 	
			WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) THEN  C.C_NAME 
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
 		END),C.C_NAME,
		(CASE 
			WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) THEN 
				(CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) THEN C.C_NAME 
				 	ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
			END)
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
		END),
		CSLIT_QTY_SHIPPED,PART_NUMBER,CSLNT_QTY_PULL,
		LABEL,CST_SHIP_DATE,INV_NO,LT.LOT_NUMBER, LT.Lot_Date_Code
	FROM
		CUSTOMER_OPEN_ORDER_LINEITEM COOL 
		INNER JOIN SALES_ORDER SO ON COOL.SALESORDER_ID = SO.SALESORDER_ID
		INNER JOIN CUSTOMER C ON 
			SO.CUSTOMER_ID = C.CUSTOMER_ID 
	        INNER JOIN CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT ON
	        	COOL.LINEITEM_ID = CSLIT.LINEITEM_ID
	     	INNER JOIN  PRODUCT P ON
	        	COOL.PRODUCT_ID = P.PRODUCT_ID
	     	INNER JOIN CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT ON
	        	CSLIT.CSLIT_ID = CSLNT.CSLIT_ID
	     	INNER JOIN CUSTOMER_SHIPPING_TABLE CST ON
	        	CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID
		INNER JOIN LOT_TABLE LT ON
			CSLNT.LOT_ID = LT.LOT_ID	
	WHERE 
		 CST.CST_STATUS= ''SHIPPED'' AND CSLIT.CSLIT_STATUS = ''SHIPPED'' 
		 AND  CSLNT_QTY_PULL > 0 '

	IF @CUSTOMERIDS IS NOT NULL AND @CUSTOMERIDS <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND C.CUSTOMER_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( '''+ @CUSTOMERIDS+''') )'
	END
	
	IF @PRODUCTIDS IS NOT NULL AND @PRODUCTIDS <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND COOL.PRODUCT_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( '''+ @PRODUCTIDS+''') )'
	END

	IF @SONUMBER IS NOT NULL AND @SONUMBER <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND SALESORDER_NO = '''+ @SONUMBER+''''
	END

	IF @LOTNUMBER IS NOT NULL AND @LOTNUMBER <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.LOT_NUMBER = '''+ @LOTNUMBER+''''
	END

	IF @ORDERTYPE IS NOT NULL AND LEN(LTRIM(RTRIM(@ORDERTYPE))) > 0
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND SO.SO_IS_SAMPLE = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	END

	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING +' AND CST_SHIP_DATE >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END

	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING +' AND CST_SHIP_DATE <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
	
	--PRINT @SQLSTRING
	EXEC (@SQLSTRING)

	IF @REPORT_TYPE = 'XLREPORT'
	BEGIN
		SELECT 	
			CUSTOMER,[END CUSTOMER], [END USER],[SALEORDER NO],[LINE ITEM NUMBER], 
			[CUSTOMER PO NO],[PACKING SLIP NO], 
			[SHIP DATE],[PART NUMBER],[COOL QTY] AS [Order QTY], 
			[QTY SHIPPED], [LOT NUMBER],[DATE CODE], [LOT QTY PULLED],[INVOICE NUMBER] 
		FROM 
			#SHIPMENT_TABLE_BY_LOT_NUMBER
		ORDER BY [SALEORDER NO],[LINE ITEM NUMBER],[LOT NUMBER]
	END
	ELSE
	BEGIN
		SELECT 	
			 [LINE ITEM NUMBER],[COOL QTY],[UNIT COST],[SALEORDER NO], [CUSTOMER PO NO],
			 [END CUSTOMER],CUSTOMER,[END USER],[QTY SHIPPED],[PART NUMBER],[LOT QTY PULLED],
			 [PACKING SLIP NO],[SHIP DATE],[INVOICE NUMBER],[LOT NUMBER] 
		FROM 
			#SHIPMENT_TABLE_BY_LOT_NUMBER
	END

END